package batch.promionet;

import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.ObjectUtils;
import org.apache.log4j.*;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.*;
import java.io.*;
import java.util.Date;

public class promionetFileOfProductNewsletter
{
    final String TODAY = DateFormatUtils.format(new Date(), "yyyyMMdd");
    final String FILEPATH = "/home01/sfc/batch/promionet/data/";

    private OutputStreamWriter oswArrFile[] = null;
    private OutputStreamWriter oswFile  = null;
    private int                j = 0;

    String strCountry = "";     //Which Country
    String strSplit   = "";     //Separate file or Not
    String strFileGB  = "";

    int iSplit = 0;

    public promionetFileOfProductNewsletter(String strPrCountry, String strPrSplit) {
        strCountry = (strPrCountry.equals("all")) ? "" : StringUtils.defaultString(strPrCountry);
        strSplit   = StringUtils.defaultString(strPrSplit);
    }

    public static void main(String [] args) {
        Logger.getLogger("promionet.promionetFileOfProductNewsletter").info("( * ) Start.");

        promionetFileOfProductNewsletter ptFile = null;

        if( args.length == 2 ){
            ptFile = new promionetFileOfProductNewsletter(args[0], args[1]);
        }
        else{
            System.exit(-1);
        }

        ptFile.startMake();

        Logger.getLogger("promionet.promionetFileOfProductNewsletter").info("( * ) End.");
    }

    private void startMake(){
        strFileGB = "10493";

        if(strCountry.length() > 0){
            strFileGB += "_" + strCountry;
        }
        else{
            strFileGB += "_all";
        }

        strFileGB += "_d_" + TODAY;

        try {
            if(!strSplit.equals("")){
                iSplit = Integer.parseInt(strSplit);
                String[] dataFileNm = new String[iSplit];
                oswArrFile = new OutputStreamWriter[iSplit];

                for(int i=0;i<iSplit;i++){
                    dataFileNm[i] = FILEPATH + strFileGB + "_" + (i+1) + ".csv";
                }

                //To make Data File by 'UTF-8'
                for(int i=0;i<iSplit;i++){
                    oswArrFile[i] = new OutputStreamWriter(new FileOutputStream(dataFileNm[i], false), "UTF-8");
                }
            }
            else{
                String dataFileNm = FILEPATH + strFileGB + ".csv";

                //To make Data File by 'UTF-8'
                oswFile = new OutputStreamWriter(new FileOutputStream(dataFileNm, false), "UTF-8");
            }

            getUsers();

        }
        catch(Exception ex) {
            ex.printStackTrace();
            Logger.getLogger("promionet.promionetFileOfProductNewsletter").info(ex.toString());
        }
        finally {
            if(oswArrFile != null)  try { for(int x=0;x<oswArrFile.length;x++) oswArrFile[x].close(); }  catch(Exception ex) { ex.printStackTrace(); }
            if(oswFile != null) try { oswFile.close(); } catch(Exception ex) { ex.printStackTrace(); }
        }
    }


    //Obtain Users from DB.
    private void getUsers() throws Exception{
        Connection         conn = null;
        PreparedStatement  pstmt = null;
        ResultSet          rs = null;
        ResultSetMetaData  rsmd = null;

        String  strID           = "";
        String  strFIRSTNAME    = "";
        String  strLASTNAME     = "";
        String  strGENDER       = "";
        String  strEMAIL        = "";
        String  strREG_COUNTRY  = "";

        String  strCOUNTRY      = "";
        String  strLANGUAGE     = "";
        String  strHP_NUM       = "";
        String  strHP_BRAND     = "";
        String  strHP_MODEL     = "";
        String  strBIRTH        = "";
        String  strUSER_NO      = "";

        StringBuffer  sbFullSt = new StringBuffer();

        StringBuffer  strQuery  = new StringBuffer();
        float  flCnt           = 0;
        float  flEach          = 0;

        StringBuffer strBufferHead1 = new StringBuffer()
                                     .append("SELECT  COUNT(ID)                                                                                                 \n")
                                     .append("  FROM (                                                                                                          \n");

        StringBuffer strBufferHead2 = new StringBuffer()
                                     .append("SELECT  ID,                                                                                                       \n")
                                     .append("        DECODE(FIRSTNAME, 'WAP_TEMP', ''                                                                          \n")
                                     .append("                        , 'SIM_TEMP', ''                                                                          \n")
                                     .append("                        , REPLACE(REPLACE(FIRSTNAME, CHR(10), ''), CHR(13), '')) FIRSTNAME,                       \n")
                                     .append("        DECODE(LASTNAME, 'WAP_TEMP', ''                                                                           \n")
                                     .append("                       , 'SIM_TEMP', ''                                                                           \n")
                                     .append("                       , REPLACE(REPLACE(LASTNAME, CHR(10), ''), CHR(13), '')) LASTNAME,                          \n")
                                     .append("        DECODE(GENDER, 'W', ''                                                                                    \n")
                                     .append("                     , 'S', ''                                                                                    \n")
                                     .append("                     , DECODE(GENDER, 'M', '1', '2')) GENDER,                                                     \n")
                                     .append("        REPLACE(REPLACE(EMAIL, CHR(10), ''), CHR(13), '') EMAIL,                                                  \n")
                                     .append("        REPLACE(REPLACE(COUNTRY, CHR(10), ''), CHR(13), '') COUNTRY,                                              \n")
                                     .append("        DECODE(LANGUAGE, NULL, DECODE(REG_COUNTRY, 'gbr', 'ENG'                                                   \n")
                                     .append("                                                 , 'fra', 'FRA'                                                   \n")
                                     .append("                                                 , 'ger', 'GER'                                                   \n")
                                     .append("                                                 , 'eur', 'ENG'                                                   \n")
                                     .append("                                                 , 'esp', 'ESP'                                                   \n")
                                     .append("                                                 , 'nld', 'NLD'                                                   \n")
                                     .append("                                                 , 'prt', 'PRT'                                                   \n")
                                     .append("                                                 , 'rus', 'RUS'                                                   \n")
                                     .append("                                                 , 'uan', 'RUS'                                                   \n")
                                     .append("                                                 , 'bel', 'NLD'                                                   \n")
                                     .append("                                                 , 'ita', 'ITA'                                                   \n")
                                     .append("                                                 , 'grc', 'GRC'                                                   \n")
                                     .append("                                                 , 'nrd', 'ENG')                                                  \n")
                                     .append("                             , LANGUAGE)                            LANGUAGE,                                     \n")
                                     .append("        REPLACE(REPLACE(HP_NUM, CHR(10), ''), CHR(13), '') HP_NUM,                                                \n")
                                     .append("        REPLACE(REPLACE(HP_BRAND, CHR(10), ''), CHR(13), '') HP_BRAND,                                            \n")
                                     .append("        REPLACE(REPLACE(HP_MODEL, CHR(10), ''), CHR(13), '') HP_MODEL,                                            \n")
                                     .append("        DECODE(BIRTH, 'WAP_TEMP', ''                                                                              \n")
                                     .append("                    , 'SIM_TEMP', ''                                                                              \n")
                                     .append("                    , DECODE(TRANSLATE(REPLACE(REPLACE(BIRTH, CHR(10), ''), CHR(13), '')                          \n")
                                     .append("                                      , '0123456789' , '@@@@@@@@@@')                                              \n")
                                     .append("                           , '@@@@@@@@', SUBSTR(BIRTH, 1, 4)||'-'||SUBSTR(BIRTH, 5, 2)||'-'||SUBSTR(BIRTH, 7, 2)  \n")
                                     .append("                           , NULL))                                                 BRITH,                        \n")
                                     .append("        REPLACE(REPLACE(REG_COUNTRY, CHR(10), ''), CHR(13), '') REG_COUNTRY,                                      \n")
                                     .append("        USER_NO                                                                                                   \n");

        StringBuffer strBuffer = new StringBuffer()
                                     .append("  FROM  G_MB_MEMBER                                                                                               \n")
                                     .append(" WHERE (LTRIM(RTRIM(EMAIL)) IS NOT NULL AND EMAIL <> 'WAP_TEMP')                                                  \n")
                                     .append("   AND  DELETED IS NULL                                                                                           \n");

        //According to Renewal
        if(   !strCountry.equals("gbr")
           && !strCountry.equals("ger")
           && !strCountry.equals("nld")
           && !strCountry.equals("rus")
           && !strCountry.equals("bel")
           && !strCountry.equals("zaf")
           && !strCountry.equals("ita")
           && !strCountry.equals("prt")
           && !strCountry.equals("nrd")
           && !strCountry.equals("esp")
           && !strCountry.equals("uan")){
            strBuffer.append("                   AND  NEWS_LETTER = 'Y'                                                                                         \n");
        }
        else{
            strBuffer.append("                   AND  USER_NO IN (SELECT USER_NO FROM G_MB_OPTIN WHERE CODE = '0113003')                                        \n");
        }

        if (strCountry.equals("bel")) {
            strBuffer.append("                   AND  COUNTRY <> '0102042'                                                                                      \n");
        }

        if(!strCountry.equals("")){
            strBuffer.append("                   AND  REG_COUNTRY = '" + strCountry + "'                                                                        \n");
        }
        else{
            strBuffer.append("                   AND  REG_COUNTRY IN ('eur', 'grc', 'fra')                                                                      \n");
        }

        if(strCountry.equals("")){
            strBuffer.append("                UNION ALL                                                                                                         \n")
                     .append(strBufferHead2)
                     .append("                  FROM  G_MB_MEMBER                                                                                               \n")
                     .append("                 WHERE (LTRIM(RTRIM(EMAIL)) IS NOT NULL AND EMAIL <> 'WAP_TEMP')                                                  \n")
                     .append("                   AND  DELETED IS NULL                                                                                           \n")
                     .append("                   AND  REG_COUNTRY IN ('gbr', 'ger', 'nld', 'rus', 'bel', 'ita', 'prt', 'esp', 'nrd', 'uan')                     \n")
                     .append("                   AND  USER_NO IN (SELECT USER_NO FROM G_MB_OPTIN WHERE CODE = '0113001')                                        \n");
        }

        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            conn = DriverManager.getConnection("jdbc:oracle:thin:@eu-anydb:1521:ORAEU","gsms","any02gs");

            if(!strSplit.equals("")){
                //For Count the data
                strQuery = strBufferHead1.append(strBuffer)
                                         .append(")");
                pstmt = conn.prepareStatement(strQuery.toString());
                rs = pstmt.executeQuery();
                //System.out.println("strQuery===" + strQuery);


                if(rs.next()){
                    flCnt = rs.getFloat(1);
                }

                rs = null;
                pstmt.clearParameters();

                flEach = Math.round(flCnt / iSplit);
            }

            strQuery = strBufferHead2.append(strBuffer);
            pstmt = conn.prepareStatement(strQuery.toString());
            rs = pstmt.executeQuery();
            //System.out.println("strQuery===" + strQuery);

            while(rs.next()){
                rsmd = rs.getMetaData();
                for(int i = 1; i <= rsmd.getColumnCount(); i++) {
                    switch(i){
                        case 1 :
                            strID          = ObjectUtils.toString(rs.getObject(i), "");
                            break;
                        case 2 :
                            strFIRSTNAME   = ObjectUtils.toString(rs.getObject(i), "");
                            break;
                        case 3 :
                            strLASTNAME    = ObjectUtils.toString(rs.getObject(i), "");
                            break;
                        case 4 :
                            strGENDER      = ObjectUtils.toString(rs.getObject(i), "");
                            break;
                        case 5 :
                            strEMAIL       = ObjectUtils.toString(rs.getObject(i), "");
                            break;
                        case 6 :
                            strCOUNTRY     = ObjectUtils.toString(rs.getObject(i), "");
                            break;
                        case 7 :
                            strLANGUAGE    = ObjectUtils.toString(rs.getObject(i), "");
                            break;
                        case 8 :
                            strHP_NUM      = ObjectUtils.toString(rs.getObject(i), "");
                            break;
                        case 9 :
                            strHP_BRAND    = ObjectUtils.toString(rs.getObject(i), "");
                            break;
                        case 10 :
                            strHP_MODEL    = ObjectUtils.toString(rs.getObject(i), "");
                            break;
                        case 11 :
                            strBIRTH       = ObjectUtils.toString(rs.getObject(i), "");
                            break;
                        case 12 :
                            strREG_COUNTRY = ObjectUtils.toString(rs.getObject(i), "");
                            break;
                        case 13 :
                            strUSER_NO     = ObjectUtils.toString(rs.getObject(i), "");
                            break;
                    }
                }

                //Make Sentences
                sbFullSt.setLength(0);

                sbFullSt.append("\"")
                        .append(this.getClientID(strREG_COUNTRY)).append("\";\"")   //clientID
                        .append(strUSER_NO                      ).append("\";\"")   //custID
                        .append(strEMAIL                        ).append("\";\"")   //mail
                        .append(strID                           ).append("\";\"")   //nick
                        .append(strFIRSTNAME                    ).append("\";\"")   //firstname
                        .append(strLASTNAME                     ).append("\";\"")   //name
                        .append(strGENDER                       ).append("\";\"")   //gender
                        .append(""                              ).append("\";\"")   //street
                        .append(""                              ).append("\";\"")   //zipcode
                        .append(""                              ).append("\";\"")   //location
                        .append(strCOUNTRY                      ).append("\";\"")   //country
                        .append(strLANGUAGE                     ).append("\";\"")   //Language
                        .append(strHP_NUM                       ).append("\";\"")   //Telephone
                        .append(strHP_BRAND                     ).append("\";\"")   //Manufacturer
                        .append(strHP_MODEL                     ).append("\";\"")   //Model Name
                        .append(strBIRTH                        ).append("\";\"")   //Birthday
                        .append(""                              ).append("\";\"")   //special_1
                        .append(""                              ).append("\";\"")   //special_2
                        .append(""                              ).append("\"\n");   //special_3

                //Write down sentences on the file
                if(!strSplit.equals("")){
                    int iTemp = 0;

                    if(j % flEach == 0 && (j / flEach) > 1){
                        iTemp = (int)(j / flEach) - 1;
                    }
                    else{
                        iTemp = (int)(j / flEach);
                    }

                    //System.out.println("iTemp===" + iTemp);
                    oswArrFile[iTemp].write(sbFullSt.toString());

                }
                else{
                    oswFile.write(sbFullSt.toString());
                }

                j++;
            }

            Logger.getLogger("promionet.promionetFileOfProductNewsletter").info("Total Count : [" + strFileGB + "] - " + j);
        }
        finally {
            if( rs != null)    try { rs.close(); }    catch (Exception ex) { ex.printStackTrace(); }
            if( pstmt != null) try { pstmt.close(); } catch (Exception ex) { ex.printStackTrace(); }
            if( conn != null)  try { conn.close(); }  catch (Exception ex) { ex.printStackTrace(); }
        }
    }

    //Make ClientID per REG_COUNTRY
    private String getClientID(String strREG_COUNTRY){
        if(strREG_COUNTRY.equals("gbr")){
            return "10515";
        }
        if(strREG_COUNTRY.equals("ger")){
            //return "10494";
            return "10605";
        }
        if(strREG_COUNTRY.equals("eur")){
            return "10493";
        }
        if(strREG_COUNTRY.equals("esp")){
            return "10511";
        }
        if(strREG_COUNTRY.equals("nld")){
            return "10590";
        }
        if(strREG_COUNTRY.equals("prt")){
            return "10512";
        }
        if(strREG_COUNTRY.equals("rus")){
            return "10517";
        }
        if(strREG_COUNTRY.equals("uan")){
            return "10518";
        }
        if(strREG_COUNTRY.equals("bel")){
            return "10591";
        }
        if(strREG_COUNTRY.equals("ita")){
            return "10510";
        }
        if(strREG_COUNTRY.equals("grc")){
            return "10516";
        }
        if(strREG_COUNTRY.equals("nrd")){
            return "10514";
        }
        if(strREG_COUNTRY.equals("fra")){
            return "10560";
        }

        return "99999";
    }
}